﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using CS2102_Music.Logic.LogicModels;

namespace CS2102_Music.Logic
{
    public partial class Logic
    {
        // Functions with SQL
        public List<AlbumL> GetNewAlbums()
        {
            List<AlbumL> newAlbums = new List<AlbumL>();
            AlbumL album = new AlbumL();
            string query = "SELECT * FROM album_owned_by a WHERE a.active = 'Y' ORDER BY a.releasedate DESC, a.title LIMIT 10";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                album.Title = result.GetString(0);
                album.ReleaseDate = result.GetDateTime(1);
                album.CoverPhoto = result.GetString(2);
                album.Price = Math.Round((result.GetDouble(3)), 2);
                album.ID = result.GetInt32(4);
                album.Active = result.GetString(5);

                newAlbums.Add(album);

                album = new AlbumL();
            }

            result.Close();

            return newAlbums;
        }
        public AlbumL GetAlbumByID(int albumID)
        {
            AlbumL album = new AlbumL();

            // get album details
            string query1 = "SELECT * FROM album_owned_by a WHERE a.active = 'Y' AND a.albumID = @albumID";
            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query1
                                                                            , new QueryParameter("@albumID", albumID.ToString())
                                                                            );

            while (result1.Read())
            {
                album.Title = result1.GetString(0);
                album.ReleaseDate = result1.GetDateTime(1);
                album.CoverPhoto = result1.GetString(2);
                album.Price = Math.Round((result1.GetDouble(3)), 2);
                album.ID = result1.GetInt32(4);
                album.Active = result1.GetString(5);
            }

            result1.Close();



            // get songs list in the album
            string query2 = "SELECT * FROM recorded_in r, song_owned_by s WHERE s.songID = r.song AND r.album = @albumID AND s.active = 'Y' ORDER BY r.trackNo";
            SongL song = new SongL();

            MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                            , new QueryParameter("@albumID", albumID.ToString())
                                                                            );

            while (result2.Read())
            {
                song.ID = result2.GetInt32(0);
                song.Title = result2.GetString(4);
                song.ReleaseDate = result2.GetDateTime(5);
                song.Price = Math.Round((result2.GetDouble(6)),2);
                song.Genre = result2.GetString(7);
                song.Composer = result2.GetString(8);
                song.Lyricist = result2.GetString(9);
                song.Duration = result2.GetInt32(10);
                song.Active = result2.GetString(11);

                album.Songs.Add(song);
                song = new SongL();
            }

            result2.Close();


          
            // get artist(s) of each song
            string query3 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < album.Songs.Count(); i++)
            {
                songID = album.Songs[i].ID;

                MySqlDataReader result3 = databaseConnector.ExecuteReaderQuery(query3
                                                                               , new QueryParameter("@songID", songID.ToString())
                                                                               );

                while (result3.Read())
                {
                    artist.Name = result3.GetString(0);
                    artist.Photo = result3.GetString(1);
                    artist.ID = result3.GetInt32(2);

                    album.Songs[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result3.Close();
            }


            return album;
        }
        public bool CheckUserBoughtAlbum(string user, int albumID)
        {
            bool exist = false;
            int count = 0;
            string query = "SELECT COUNT(*) FROM buyalbum ba WHERE ba.buyer = @userEmail AND ba.album = @albumID";
            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query
                                                                          , new QueryParameter("@userEmail", user)
                                                                          , new QueryParameter("@albumID", albumID.ToString())
                                                                          );
            while (result.Read())
            {
                count = result.GetInt32(0);
            }

            result.Close();

            if (count == 0)
                exist = false;
            else
                exist = true;

            return exist;
        }
        public void BuyAlbum(string user, int songID, DateTime date, double price)
        {
            string query = "INSERT INTO buyalbum VALUES(@userEmail, @songID, " + Utilities.ToSQLDateString(date) + ", @price, 0)";
            databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@userEmail", user)
                                               , new QueryParameter("@songID", songID.ToString())
                                               , new QueryParameter("@price", price.ToString())
                                              );
        }
        public bool DeleteAlbum(int albumID)
        {
            string query = "UPDATE album_owned_by SET active = 'N' WHERE albumID = @albumID";

            bool delete = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@albumID", albumID.ToString())
                                              );
            return delete;
        }
        public void EditAlbumDetails(int albumID, string title, string releaseDate, double price)
        {
            string query = "UPDATE album_owned_by SET title = @title WHERE albumID = @albumID";

            bool update1 = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@title", title)
                                               , new QueryParameter("@albumID", albumID.ToString())
                                              );

            query = "UPDATE album_owned_by SET releaseDate = @releaseDate WHERE albumID = @albumID";
            bool update2 = databaseConnector.ExecuteNonQuery(query
                                              , new QueryParameter("@releaseDate", releaseDate)
                                              , new QueryParameter("@albumID", albumID.ToString())
                                             );

            query = "UPDATE album_owned_by SET price = @price WHERE albumID = @albumID";
            bool update3 = databaseConnector.ExecuteNonQuery(query
                                              , new QueryParameter("@price", price.ToString())
                                              , new QueryParameter("@albumID", albumID.ToString())
                                             );


        }
        public void AddSongToAlbum(int albumID, int songID)
        {
            int trackNum = 1;

            // get the next track num
            string query1 = "SELECT MAX(TRACKNO)+1 FROM recorded_in ri WHERE ri.album = @albumID";

            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query1
                                                                           , new QueryParameter("@albumID", albumID.ToString())
                                                                           );

            while (result1.Read())
            {
                trackNum = result1.GetInt32(0);
            }

            result1.Close();

            string query2 = "INSERT INTO recorded_in VALUES(@songID, @albumID, @trackNum)";

                databaseConnector.ExecuteNonQuery(query2
                                              , new QueryParameter("@songID", songID.ToString())
                                              , new QueryParameter("@albumID", albumID.ToString())
                                              , new QueryParameter("@trackNum", trackNum.ToString())
                                             );

        }
        public bool DeleteSongFromAlbum(int albumID, int songID)
        {
            int trackNum = 0;
            int maxTrackNum = 0;
            bool update = true;

            // get track num of this song
            string query1 = "SELECT ri.trackNo FROM recorded_in ri WHERE ri.album = @albumID AND ri.song = @songID";
            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query1
                                                                            , new QueryParameter("@albumID", albumID.ToString())
                                                                            , new QueryParameter("@songID", songID.ToString())
                                                                            );
            while (result1.Read())
            {
                trackNum = result1.GetInt32(0);
            }

            result1.Close();

            // get max track num
            string query2 = "SELECT MAX(TRACKNO)+1 FROM recorded_in ri WHERE ri.album = @albumID";
            MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                            , new QueryParameter("@albumID", albumID.ToString())
                                                                            );
            while (result2.Read())
            {
                maxTrackNum = result2.GetInt32(0);
            }

            result2.Close();

            // if max track num is 2- means if delete this song, there will be no song -> DONT allow deletion
            if (maxTrackNum == 2)
                update = false;

            if (update)
            {
                // delete this song from album
                string query3 = "DELETE FROM recorded_in WHERE song = @songID AND album = @albumID";

                databaseConnector.ExecuteNonQuery(query3
                                              , new QueryParameter("@songID", songID.ToString())
                                              , new QueryParameter("@albumID", albumID.ToString())
                                             );



                // shift forward the existing songs in album after this
                string query4 = "UPDATE recorded_in SET trackNo = @newTrackNum WHERE trackNo = @currTrackNum";
                for (int i = (trackNum + 1); i < maxTrackNum; i++)
                {
                    update = databaseConnector.ExecuteNonQuery(query4
                                             , new QueryParameter("@newTrackNum", (i - 1).ToString())
                                             , new QueryParameter("@currTrackNum", i.ToString())
                                             );
                }
            }

            return update;
        }
        public void CreateAlbum(string title, string releaseDate, double price, int songID)
        {
            int newAlbumID = 0;
            // get next ID for album
            string query = "SELECT COUNT(*) FROM album_owned_by";
            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                newAlbumID = result.GetInt32(0);
            }

            result.Close();

            query = "CALL createAlbum (@title, @releaseDate, 'test.com', @price, @albumID, 'Y')";

            databaseConnector.ExecuteNonQuery(query
                                              , new QueryParameter("@title", title)
                                              , new QueryParameter("@releaseDate", releaseDate)
                                              , new QueryParameter("@price", price.ToString())
                                              , new QueryParameter("@albumID", newAlbumID.ToString())
                                              );

            //insert song into album
            query = "INSERT INTO recorded_in values(@songID, @albumID, 1)";
            databaseConnector.ExecuteNonQuery(query
                                              , new QueryParameter("@songID", songID.ToString())
                                              , new QueryParameter("@albumID", newAlbumID.ToString())
                                              );

        }
        public List<AlbumL> WorstSellingAlbum(out int number)
        {
            AlbumL album = new AlbumL();
            number = 0;
            List<AlbumL> worst = new List<AlbumL>();

            // those that no one bought yet.
            string query1 = "SELECT a1.albumID, a1.title, a1.price, a1.releaseDate, 0 FROM album_owned_by a1 WHERE a1.active = 'Y' AND NOT EXISTS (SELECT * FROM buyalbum ba WHERE a1.albumID = ba.album GROUP BY a1.albumID, a1.title) ORDER BY a1.title , a1.albumID";

            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query1);

            while (result1.Read())
            {
                album.ID = result1.GetInt32(0);
                album.Title = result1.GetString(1);
                album.Price = Math.Round((result1.GetDouble(2)), 2);
                album.ReleaseDate = result1.GetDateTime(3);
                number = result1.GetInt32(4);

                worst.Add(album);
                album = new AlbumL();
            }

            result1.Close();

            // check if all albums are sold -> if yes, find the least sold
            if (worst.Count() == 0)
            {
                string query2 = "SELECT a.albumID, a.title, a.price, a.releaseDate, COUNT(*) FROM album_owned_by a, buyalbum ba WHERE a.albumid = ba.ALBUM AND a.active = 'Y' GROUP BY a.albumid, a.title, a.price, a.releaseDate HAVING COUNT(*) <= ALL(SELECT COUNT(*) FROM album_owned_by a2, buyalbum ba2 WHERE a2.albumid = ba2.ALBUM AND a2.active = 'Y' GROUP BY a2.albumid, a2.title) ORDER BY a.title , a.albumID";

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2);

                while (result2.Read())
                {
                    album.ID = result2.GetInt32(0);
                    album.Title = result2.GetString(1);
                    album.Price = Math.Round((result2.GetDouble(2)), 2);
                    album.ReleaseDate = result2.GetDateTime(3);
                    number = result2.GetInt32(4);

                    worst.Add(album);
                    album = new AlbumL();
                }

                result2.Close();
            }

            return worst;
        }
        public List<AlbumL> GetDeletedAlbums()
        {
            List<AlbumL> deleted = new List<AlbumL>();
            AlbumL album = new AlbumL();

            string query = "SELECT * FROM album_owned_by a WHERE a.active = 'N' ORDER BY a.title, a.albumID";


            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query);

            while (result1.Read())
            {
                album.Title = result1.GetString(0);
                album.ReleaseDate = result1.GetDateTime(1);
                album.Price = Math.Round((result1.GetDouble(3)),2);
                album.ID = result1.GetInt32(4);
                album.Active = result1.GetString(5);

                deleted.Add(album);
                album = new AlbumL();
            }

            result1.Close();

            return deleted;
        }
        public List<AlbumL> SearchAlbum(string search)
        {
            List<AlbumL> resultList = new List<AlbumL>();
            AlbumL album = new AlbumL();
            string query = "SELECT * FROM album_owned_by a WHERE a.active = 'Y' AND a.title like @title ORDER BY a.title";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query
                                                                            , new QueryParameter("@title", "%" + search + "%")
                                                                            );

            while (result.Read())
            {
                album.Title = result.GetString(0);
                album.ReleaseDate = result.GetDateTime(1);
                album.CoverPhoto = result.GetString(2);
                album.Price = Math.Round((result.GetDouble(3)), 2);
                album.ID = result.GetInt32(4);
                album.Active = result.GetString(5);

                resultList.Add(album);

                album = new AlbumL();
            }

            result.Close();

            return resultList;
        }
        public List<AlbumL> HottestAlbum()
        {
            List<AlbumL> list = new List<AlbumL>();

            AlbumL album = new AlbumL();
            string query = "SELECT a.albumID, a.title, a.price, a.releaseDate FROM album_owned_by a, buyalbum ba WHERE a.albumid = ba.ALBUM AND a.active = 'Y' GROUP BY a.albumid, a.title, a.price, a.releaseDate HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM album_owned_by a2, buyalbum ba2 WHERE a2.albumid = ba2.ALBUM AND a2.active = 'Y' GROUP BY a2.albumid, a2.title)";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                album.ID = result.GetInt32(0);
                album.Title = result.GetString(1);
                album.Price = Math.Round((result.GetDouble(2)), 2);
                album.ReleaseDate = result.GetDateTime(3);

                list.Add(album);

                album = new AlbumL();
            }

            result.Close();

            return list;
        }
        public List<AlbumL> Below10Deals()
        {
            List<AlbumL> list = new List<AlbumL>();

            AlbumL album = new AlbumL();
            string query = "SELECT * FROM album_owned_by a WHERE a.active = 'Y' AND a.price < 10 ORDER BY a.price, a.title";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                album.Title = result.GetString(0);
                album.ReleaseDate = result.GetDateTime(1);
                album.Price = Math.Round((result.GetDouble(3)), 2);          
                album.ID = result.GetInt32(4);

                list.Add(album);

                album = new AlbumL();
            }

            result.Close();

            return list;
        }
        public List<AlbumL> BestSellersAlbums()
        {
            List<AlbumL> list = new List<AlbumL>();

            AlbumL album = new AlbumL();
            string query = "SELECT a.albumID, a.title, a.price, a.releaseDate, COUNT(*) FROM album_owned_by a, buyalbum ba WHERE a.albumid = ba.ALBUM AND a.active = 'Y' GROUP BY a.albumid, a.title, a.price, a.releaseDate ORDER BY COUNT(*) DESC, a.title, a.albumID LIMIT 10";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                album.ID = result.GetInt32(0);
                album.Title = result.GetString(1);
                album.Price = Math.Round((result.GetDouble(2)), 2);
                album.ReleaseDate = result.GetDateTime(3);

                list.Add(album);

                album = new AlbumL();
            }

            result.Close();

            return list;
        }
        public List<AlbumL> GetAllAlbums()
        {
            List<AlbumL> list = new List<AlbumL>();
            AlbumL album = new AlbumL();

            string query = "SELECT * FROM displayAlbums";


            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query);

            while (result1.Read())
            {
                album.Title = result1.GetString(0);
                album.ReleaseDate = result1.GetDateTime(1);
                album.Price = Math.Round((result1.GetDouble(3)), 2);
                album.ID = result1.GetInt32(4);
                album.Active = result1.GetString(5);

                list.Add(album);
                album = new AlbumL();
            }

            result1.Close();

            return list;
        }
        
            
    }
}